﻿
namespace Common
{
    public sealed class SqlQuery
    {
        private SqlQuery()
        {
            
        }

        #region Category
        //View all category
        public const string ViewAllCategory = @"SELECT CateId, CateName, CateDescription, 
                                                (SELECT COUNT(PostId) FROM Post WHERE Post.CateId = Category.CateId) AS PostCount 
                                                FROM Category";
        //View category by ID
        public const string ViewCategoryById = @"SELECT * FROM Category WHERE CateId = @CateId";
        //View category name by Id.
        public const string ViewCategoryNameById = @"SELECT CateName FROM Category WHERE CateId = @CateId";
        //View Category ID by name
        public const string ViewCategoryIdByName = @"SELECT CateId FROM Category WHERE CateName = @CateName";
        //Delete a category
        public const string DeleteCategoryById = @"DELETE FROM Category WHERE CateId = @CateId";
        //Deactive a category
        public const string DeactivateCategoryById = @"UPDATE Category SET Status = 0 WHERE CateId = @CateId";
        //Active a category 
        public const string ActivateCategoryById = @"UPDATE Category SET Status = 1 WHERE CateId = @CateId";
        //Create a category
        public const string CreateCategory = @"INSERT INTO Category(CateName, CateDescription, Status) 
                                                VALUES(@CateName, @CateDescription, @Status)";
        public const string UpdateCategory = @"
                                                UPDATE Category 
                                                SET 
                                                    CateName = @CateName, 
                                                    CateDescription = @CateDescription, 
                                                    Status = @Status 
                                                WHERE CateId = @CateId
                                            ";


        #endregion

        #region Post

        public const string ViewPostByCategoryId = @"
                                                    SELECT  COUNT(C.CmtId) AS CommentCount, P.PostId, P.Title, P.ShortDescription, P.[Content], 
                                                            P.ImageLink, P.DatePost, P.Status, I.CateName, U.Fullname
                                                    FROM    Post AS P LEFT OUTER JOIN
                                                                 Comment AS C ON P.PostId = C.PostId INNER JOIN
                                                                 Category AS I ON P.CateId = I.CateId INNER JOIN
                                                                 [User] AS U ON P.UserId = U.UserId
                                                    WHERE   (P.Status = 1) AND (P.CateId = @CateId)
                                                    GROUP BY    P.PostId, P.Title, P.ShortDescription, P.[Content], P.ImageLink, 
                                                                P.DatePost, P.Status, I.CateName, U.Fullname
                                                    ORDER BY    P.PostId DESC
                                                    ";
        public const string ViewAllPost = @"
                                                SELECT  COUNT(C.CmtId) AS CommentCount, P.PostId, P.Title, P.ShortDescription, P.[Content], 
                                                        P.ImageLink, P.DatePost, P.Status, P.Views, I.CateName, U.Fullname
                                                FROM    Post AS P LEFT OUTER JOIN
                                                             Comment AS C ON P.PostId = C.PostId INNER JOIN
                                                             Category AS I ON P.CateId = I.CateId INNER JOIN
                                                             [User] AS U ON P.UserId = U.UserId
                                                WHERE   (P.Status = 1) AND (P.Status = 1) AND (U.Status = 1)
                                                GROUP BY    P.PostId, P.Title, P.ShortDescription, P.[Content], P.ImageLink, 
                                                            P.DatePost, P.Status, P.Views, I.CateName, U.Fullname
                                                ORDER BY    P.PostId DESC
                                            ";
        public const string ReadAPost = @"
                                            SELECT  COUNT(C.CmtId) AS CommentCount, P.PostId, P.Title, P.ShortDescription, P.[Content], 
                                                    P.ImageLink, P.DatePost, P.Status, I.CateName, U.Fullname
                                            FROM     Post AS P LEFT OUTER JOIN
                                                     Comment AS C ON P.PostId = C.PostId INNER JOIN
                                                     Category AS I ON P.CateId = I.CateId INNER JOIN
                                                     [User] AS U ON P.UserId = U.UserId
                                            WHERE   (P.PostId = @PostId) AND (P.Status = 1)
                                            GROUP BY    P.PostId, P.Title, P.ShortDescription, P.[Content], P.ImageLink, 
                                                        P.DatePost, P.Status, I.CateName, U.Fullname
                                            ORDER BY    P.PostId DESC
                                        ";
        public const string ViewPostByUserId = @"
                                                    SELECT  COUNT(C.CmtId) AS CommentCount, P.PostId, P.Title, P.ShortDescription, P.[Content], 
                                                            P.ImageLink, P.DatePost, P.Status, I.CateName, U.Fullname
                                                    FROM     Post AS P LEFT OUTER JOIN
                                                             Comment AS C ON P.PostId = C.PostId INNER JOIN
                                                             Category AS I ON P.CateId = I.CateId INNER JOIN
                                                             [User] AS U ON P.UserId = U.UserId
                                                    WHERE   (P.Status = 1) AND (P.UserId = @UserId)
                                                    GROUP BY    P.PostId, P.Title, P.ShortDescription, P.[Content], P.ImageLink, 
                                                                P.DatePost, P.Status, I.CateName, U.Fullname
                                                    ORDER BY    P.PostId DESC
                                                ";
        public const string SearchPost = @"
                                            SELECT  COUNT(C.CmtId) AS CommentCount, P.PostId, P.Title, P.ShortDescription, P.[Content], 
                                                    P.ImageLink, P.DatePost, P.Status, I.CateName, U.Fullname
                                            FROM     Post AS P LEFT OUTER JOIN
                                                     Comment AS C ON P.PostId = C.PostId INNER JOIN
                                                     Category AS I ON P.CateId = I.CateId INNER JOIN
                                                     [User] AS U ON P.UserId = U.UserId
                                            WHERE   (P.Status = 1) AND (P.Title LIKE '%'+@Title+'%')
                                            GROUP BY    P.PostId, P.Title, P.ShortDescription, P.[Content], P.ImageLink, 
                                                        P.DatePost, P.Status, I.CateName, U.Fullname
                                            ORDER BY    P.PostId DESC
                                        ";
        public const string DeletePost = @"DELETE FROM Post WHERE PostId = @PostId";
        public const string DeletePostByUserId = @"DELETE FROM Post WHERE UserId = @UserId";
        public const string CreatePost = @"
                                            INSERT INTO Post(Title, ShortDescription, [Content], ImageLink, DatePost, Status, Views, UserId, CateId) 
                                            VALUES
                                            (
                                                Title = @Title, 
                                                ShortDescription = @ShortDescription,
                                                [Content] = @Content,
                                                ImageLink = @Image,
                                                DatePost = @DatePost, 
                                                Status = @Status,
                                                Views = 0,
                                                UserId = @UserId,
                                                CateId = @CateId
                                            )";
        public const string EditPost = @"
                                        UPDATE Post
                                        SET
                                                Title = @Title,
                                                ShortDescription = @ShortDescription,
                                                [Content] = @Content,
                                                ImageLink = @ImageLink,
                                                DatePost = @DatePost,
                                                Status = @Status,
                                                CateId = @CateId
                                        WHERE   PostId = @PostId
                                        ";

        public const string UpdateViews = @"UPDATE Post SET Views += 1 WHERE PostId = @PostId";
        #endregion

        #region Comment

        public const string ViewComments = @"
                                            SELECT  CmtId, CommentContent, UserComment, CmtDate, Status 
                                            FROM    Comment 
                                            WHERE   PostId = @PostId
                                            ";
        public const string AddComment = @"
                                            INSERT INTO Comment
                                            VALUES(@UserComment, @CommentContent, @CmtDate, 1, @PostId)
                                            ";
        public const string DeleteCommentByPostId = @"DELETE FROM Comment WHERE PostId = @PostId";
        #endregion

        #region User
        //Kiem tra user co quyen trong he thong, ho tro logon.
        public const string CheckUser = @"SELECT COUNT(UserId) 
                                        FROM User 
                                        WHERE Username = @Username AND Password = @Password AND Status = 1";
        //Deactive an user.
        public const string DeactivateUser = @"UPDATE User SET Status = 0 WHERE UserId = @UserId";
        //Them user vao he thong
        public const string AddUser = @"
                                        INSERT INTO User(Username, Password, Fullname, Email, Phone, Address, Status)
                                        VALUES(@Username, @Password, @Fullname, @Email, @Phone, @Address, @Status)
                                        ";

        //Update user
        public const string UpdateUser = @"
                                            UPDATE User
                                            SET 
                                            (
                                                Username = @Username,
                                                Fullname = @Fullname,
                                                Email = @Email,
                                                Phone = @Phone,
                                                Address = @Address,
                                                Status = @Status
                                            )
                                            WHERE (UserId = @UserId)
                                            ";
        public const string ViewAllUser = @"
                                            SELECT  UserId, Username, Fullname, Email, Phone, Address, Status
                                            FROM    User WHERE 
                                            ";
        public const string ViewUserById = @"
                                            SELECT  UserId, Username, Fullname, Phone, Address, Status
                                            FROM    User
                                            WHERE   (Status = 1) AND (UserId = @UserId)
                                            ";

        #endregion
    }
}
